const connection = require('../../database/connection')

module.exports = {
    // 中心部门业务管理
    async getCenters() {
        const [centers] = await connection.execute('SELECT c.* FROM `centers` c WHERE c.`deleted_at` IS NULL ORDER BY c.`id`;')
        return centers
    },
    async getDepartments() {
        const [departments] = await connection.execute('SELECT d.*, c.`name` center_name FROM `departments` d, `centers` c WHERE c.id = d.center_id AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL ORDER BY d.id;')
        return departments
    },
    async getSalesmansTotal(data) {
        const [[total]] = await connection.execute('SELECT COUNT(*) total FROM `salesmans` s, `centers` c, `departments` d WHERE d.id = s.department_id AND c.id = s.center_id AND s.`name` LIKE "' + data + '%" AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL AND s.`deleted_at` IS NULL;')
        return total
    },
    async getSalesmans(data) {
        const [salesmans] = await connection.execute('SELECT s.*, d.`name` dept_name, c.`name` center_name FROM `salesmans` s, `centers` c, `departments` d WHERE c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL AND s.`deleted_at` IS NULL AND d.id = s.department_id AND c.id = s.center_id AND s.`name` LIKE "' + data.search + '%" ORDER BY s.id LIMIT ' + data.limit + ' OFFSET ' + data.offset + ';')
        return salesmans
    },
    async getCenterByName(data) {
        const [[center]] = await connection.execute('SELECT c.* FROM `centers` c WHERE c.`name` = "' + data + '" AND c.`deleted_at` IS NULL;')
        return center
    },
    async getCenterById(id) {
        const [[center]] = await connection.execute('SELECT c.* FROM `centers` c WHERE c.`id` = "' + id + '" AND c.`deleted_at` IS NULL;')
        return center
    },
    async rmCenterById(id) {
        const [center] = await connection.execute('UPDATE `centers` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return center
    },
    async rmDeptById(id) {
        const [dept] = await connection.execute('UPDATE `departments` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return dept
    },
    async rmDeptByCenterId(id) {
        const [dept] = await connection.execute('UPDATE `departments` SET `deleted_at` = NOW() WHERE `center_id` = "' + id + '";')
        return dept
    },
    async rmSalesmanById(id) {
        const [salesman] = await connection.execute('UPDATE `salesmans` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return salesman
    },
    async rmSalesmanByDeptId(id) {
        const [salesman] = await connection.execute('UPDATE `salesmans` SET `deleted_at` = NOW() WHERE `department_id` = "' + id + '";')
        return salesman
    },
    async rmSalesmanByCenterId(id) {
        const [salesman] = await connection.execute('UPDATE `salesmans` SET `deleted_at` = NOW() WHERE `center_id` = "' + id + '";')
        return salesman
    },
    async getDeptByName(data) {
        const [[dept]] = await connection.execute('SELECT d.*, c.`name` center_name FROM `departments` d, `centers` c WHERE c.id = d.center_id AND d.`name` = "' + data.name + '" AND c.`name` = "' + data.center + '" AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL;')
        return dept
    },
    async getDeptById(id) {
        const [[dept]] = await connection.execute('SELECT d.* FROM `departments` d WHERE d.`id` = "' + id + '" AND d.`deleted_at` IS NULL;')
        return dept
    },
    async getDeptByCenterId(id) {
        const [dept] = await connection.execute('SELECT d.* FROM `departments` d WHERE d.`center_id` = "' + id + '" AND d.`deleted_at` IS NULL;')
        return dept
    },
    async getSalesmanByDeptId(data) {
        const [salesman] = await connection.execute('SELECT s.* FROM `salesmans` s WHERE s.`department_id` = "' + data.dept_id + '" AND s.`center_id` = "' + data.center_id + '" AND s.`deleted_at` IS NULL;')
        return salesman
    },
    async getSalesman(data) {
        const [salesman] = await connection.execute('SELECT s.* FROM `salesmans` s WHERE s.`department_id` = "' + data.dept_id + '" AND s.`center_id` = "' + data.center_id + '" AND s.`deleted_at` IS NULL ORDER BY s.`id`;')
        return salesman
    },
    async getSalesmanByName(data) {
        const [[salesman]] = await connection.execute('SELECT s.*, d.`name` dept_name, c.`name` center_name FROM `salesmans` s, `departments` d, `centers` c WHERE c.id = s.center_id AND d.id = s.department_id AND c.`name` = "' + data.center + '" AND d.`name` = "' + data.dept + '" AND s.`name` = "' + data.name + '" AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL AND s.`deleted_at` IS NULL;')
        return salesman
    },
    async getSalesmanById(id) {
        const [[salesman]] = await connection.execute('SELECT s.* FROM `salesmans` s WHERE s.`id` = "' + id + '" AND s.`deleted_at` IS NULL;')
        return salesman
    },
    async getDeptById(id) {
        const [[dept]] = await connection.execute('SELECT d.* FROM `departments` d WHERE d.`id` = "' + id + '" AND d.`deleted_at` IS NULL;')
        return dept
    },
    async getDept(data) {
        const [[dept]] = await connection.execute('SELECT d.* FROM `departments` d WHERE d.`name` = "' + data.name + '" AND d.`center_id` = "' + data.center_id + '" AND d.`deleted_at` IS NULL;')
        return dept
    },
    async addDept(data) {
        const [dept] = await connection.execute('INSERT INTO `departments` (`name`, `center_id`, `created_at`) VALUES ("' + data.name + '", "' + data.center_id + '", NOW());')
        return dept
    },
    async addCenter(data) {
        const [center] = await connection.execute('INSERT INTO `centers` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return center
    },
    async addSalesman(data) {
        const [salesman] = await connection.execute('INSERT INTO `salesmans` (`name`, `center_id`, `department_id`, `created_at`) VALUES ("' + data.name + '","' + data.center_id + '","' + data.dept_id + '", NOW());')
        return salesman
    },
    async updateCenter(id, data) {
        const [center] = await connection.execute('UPDATE `centers` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + id + '";')
        return center
    },
    async updateDept(data) {
        const [dept] = await connection.execute('UPDATE `departments` SET `name` = "' + data.name + '", `center_id` = "' + data.center_id + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return dept
    },
    async updateSalesman(data) {
        const [salesman] = await connection.execute('UPDATE `salesmans` SET `name` = "' + data.name + '", `center_id` = "' + data.center_id + '", `department_id` = "' + data.dept_id + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return salesman
    },
    async updateSalesmanAll(data) {
        const [salesman] = await connection.execute('UPDATE `salesmans` SET `center_id` = "' + data.center_id + '", `updated_at` = NOW() WHERE `department_id` = "' + data.dept_id + '";')
        return salesman
    },
    // 市场管理
    async getTrials() {
        const [trials] = await connection.execute('SELECT tc.* FROM `trial_channels` tc WHERE tc.`deleted_at` IS NULL ORDER BY tc.`id`;')
        return trials
    },
    async getTrialByName(data) {
        const [[trial]] = await connection.execute('SELECT tc.* FROM `trial_channels` tc WHERE tc.`name` = "' + data + '" AND tc.`deleted_at` IS NULL;')
        return trial
    },
    async getTrialById(id) {
        const [[trial]] = await connection.execute('SELECT tc.* FROM `trial_channels` tc WHERE tc.`id` = "' + id + '" AND tc.`deleted_at` IS NULL;')
        return trial
    },
    async addTrial(data) {
        const [trial] = await connection.execute('INSERT INTO `trial_channels` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return trial
    },
    async updateTrial(data) {
        const [trial] = await connection.execute('UPDATE `trial_channels` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return trial
    },
    async rmTrialById(id) {
        const [trial] = await connection.execute('UPDATE `trial_channels` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return trial
    },
    async getCancellations() {
        const [cancellations] = await connection.execute('SELECT rc.* FROM `reasons_cancellations` rc WHERE rc.`deleted_at` IS NULL ORDER BY rc.`id`;')
        return cancellations
    },
    async getCancellationByName(data) {
        const [[cancellation]] = await connection.execute('SELECT rc.* FROM `reasons_cancellations` rc WHERE rc.`name` = "' + data + '" AND rc.`deleted_at` IS NULL;')
        return cancellation
    },
    async getCancellationById(id) {
        const [[cancellation]] = await connection.execute('SELECT rc.* FROM `reasons_cancellations` rc WHERE rc.`id` = "' + id + '" AND rc.`deleted_at` IS NULL;')
        return cancellation
    },
    async addCancellation(data) {
        const [cancellation] = await connection.execute('INSERT INTO `reasons_cancellations` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return cancellation
    },
    async updateCancellation(data) {
        const [cancellation] = await connection.execute('UPDATE `reasons_cancellations` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return cancellation
    },
    async rmCancellationById(id) {
        const [cancellation] = await connection.execute('UPDATE `reasons_cancellations` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return cancellation
    },
    async getNotSigns() {
        const [notSigns] = await connection.execute('SELECT rns.* FROM `reasons_not_signings` rns WHERE rns.`deleted_at` IS NULL ORDER BY rns.`id`;')
        return notSigns
    },
    async getNotSignByName(data) {
        const [[notSign]] = await connection.execute('SELECT rns.* FROM `reasons_not_signings` rns WHERE rns.`name` = "' + data + '" AND rns.`deleted_at` IS NULL;')
        return notSign
    },
    async getNotSignById(id) {
        const [[notSign]] = await connection.execute('SELECT rns.* FROM `reasons_not_signings` rns WHERE rns.`id` = "' + id + '" AND rns.`deleted_at` IS NULL;')
        return notSign
    },
    async addNotSign(data) {
        const [notSign] = await connection.execute('INSERT INTO `reasons_not_signings` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return notSign
    },
    async updateNotSign(data) {
        const [notSign] = await connection.execute('UPDATE `reasons_not_signings` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return notSign
    },
    async rmNotSignById(id) {
        const [notSign] = await connection.execute('UPDATE `reasons_not_signings` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return notSign
    },
    async getPromotions() {
        const [promotions] = await connection.execute('SELECT pc.* FROM `promotion_channels` pc WHERE pc.`deleted_at` IS NULL ORDER BY pc.`id`;')
        return promotions
    },
    async getPromotionByName(data) {
        const [[promotion]] = await connection.execute('SELECT pc.* FROM `promotion_channels` pc WHERE pc.`name` = "' + data + '" AND pc.`deleted_at` IS NULL;')
        return promotion
    },
    async getPromotionById(id) {
        const [[promotion]] = await connection.execute('SELECT pc.* FROM `promotion_channels` pc WHERE pc.`id` = "' + id + '" AND pc.`deleted_at` IS NULL;')
        return promotion
    },
    async addPromotion(data) {
        const [promotion] = await connection.execute('INSERT INTO `promotion_channels` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return promotion
    },
    async updatePromotion(data) {
        const [promotion] = await connection.execute('UPDATE `promotion_channels` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return promotion
    },
    async rmPromotionById(id) {
        const [promotion] = await connection.execute('UPDATE `promotion_channels` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return promotion
    },
    async getNotpasss() {
        const [notpasss] = await connection.execute('SELECT rnp.* FROM `reasons_not_passs` rnp WHERE rnp.`deleted_at` IS NULL ORDER BY rnp.`id`;')
        return notpasss
    },
    async getNotpassByName(data) {
        const [[notpasss]] = await connection.execute('SELECT rnp.* FROM `reasons_not_passs` rnp WHERE rnp.`name` = "' + data + '" AND rnp.`deleted_at` IS NULL;')
        return notpasss
    },
    async getNotpassById(id) {
        const [[notpasss]] = await connection.execute('SELECT rnp.* FROM `reasons_not_passs` rnp WHERE rnp.`id` = "' + id + '" AND rnp.`deleted_at` IS NULL;')
        return notpasss
    },
    async addNotpass(data) {
        const [notpasss] = await connection.execute('INSERT INTO `reasons_not_passs` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return notpasss
    },
    async updateNotpass(data) {
        const [notpasss] = await connection.execute('UPDATE `reasons_not_passs` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return notpasss
    },
    async rmNotpassById(id) {
        const [notpasss] = await connection.execute('UPDATE `reasons_not_passs` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return notpasss
    },
    async getDistributions() {
        const [distributions] = await connection.execute('SELECT d.* FROM `distributions` d WHERE d.`deleted_at` IS NULL ORDER BY d.`id`;')
        return distributions
    },
    async getDistributionByName(data) {
        const [[distribution]] = await connection.execute('SELECT d.* FROM `distributions` d WHERE d.`name` = "' + data + '" AND d.`deleted_at` IS NULL;')
        return distribution
    },
    async getDistributionById(id) {
        const [[distribution]] = await connection.execute('SELECT d.* FROM `distributions` d WHERE d.`id` = "' + id + '" AND d.`deleted_at` IS NULL;')
        return distribution
    },
    async addDistribution(data) {
        const [distribution] = await connection.execute('INSERT INTO `distributions` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return distribution
    },
    async updateDistribution(data) {
        const [distribution] = await connection.execute('UPDATE `distributions` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return distribution
    },
    async rmDistributionById(id) {
        const [distribution] = await connection.execute('UPDATE `distributions` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return distribution
    },
    async getWarrantMans() {
        const [warrantMans] = await connection.execute('SELECT wm.* FROM `warrant_mans` wm WHERE wm.`deleted_at` IS NULL ORDER BY wm.`id`;')
        return warrantMans
    },
    async getWarrantManByName(data) {
        const [[warrantMan]] = await connection.execute('SELECT wm.* FROM `warrant_mans` wm WHERE wm.`name` = "' + data + '" AND wm.`deleted_at` IS NULL;')
        return warrantMan
    },
    async getWarrantManById(id) {
        const [[warrantMan]] = await connection.execute('SELECT wm.* FROM `warrant_mans` wm WHERE wm.`id` = "' + id + '" AND wm.`deleted_at` IS NULL;')
        return warrantMan
    },
    async addWarrantMan(data) {
        const [warrantMan] = await connection.execute('INSERT INTO `warrant_mans` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return warrantMan
    },
    async updateWarrantMan(data) {
        const [warrantMan] = await connection.execute('UPDATE `warrant_mans` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return warrantMan
    },
    async rmWarrantManById(id) {
        const [warrantMan] = await connection.execute('UPDATE `warrant_mans` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return warrantMan
    },
    async getNegotiators() {
        const [negotiators] = await connection.execute('SELECT n.* FROM `negotiators` n WHERE n.`deleted_at` IS NULL ORDER BY n.`id`;')
        return negotiators
    },
    async getNegotiatorByName(data) {
        const [[negotiator]] = await connection.execute('SELECT n.* FROM `negotiators` n WHERE n.`name` = "' + data + '" AND n.`deleted_at` IS NULL;')
        return negotiator
    },
    async getNegotiatorById(id) {
        const [[negotiator]] = await connection.execute('SELECT n.* FROM `negotiators` n WHERE n.`id` = "' + id + '" AND n.`deleted_at` IS NULL;')
        return negotiator
    },
    async addNegotiator(data) {
        const [negotiators] = await connection.execute('INSERT INTO `negotiators` (`name`, `created_at`) VALUES ("' + data.name + '", NOW());')
        return negotiators
    },
    async updateNegotiator(data) {
        const [negotiator] = await connection.execute('UPDATE `negotiators` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return negotiator
    },
    async rmNegotiatorById(id) {
        const [negotiator] = await connection.execute('UPDATE `negotiators` SET `deleted_at` = NOW() WHERE `id` = "' + id + '";')
        return negotiator
    },

};

